﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTOQLDV;
using System.Data;
using System.Data.OleDb;

namespace DAOQLDV
{
    public class DAOPhuong
    {
        public static List<DTOPhuong> LayDanhSachPhuong()
        {
            List<DTOPhuong> dsPhuong = new List<DTOPhuong>();
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select * From Phuong";
                OleDbDataReader dr = da.ExecuteQuery(sql);
                while (dr.Read())
                {
                    DTOPhuong dtoP = new DTOPhuong();
                    dtoP.MaPhuong = (int)dr["MaPhuong"];
                    dtoP.TenPhuong = (String)dr["TenPhuong"];
                    dsPhuong.Add(dtoP);
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return dsPhuong;
        }
        public static DTOPhuong LayThongTinPhuong(String tenPhuong)
        {
            DTOPhuong dtoPhuong = new DTOPhuong();
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select * From Phuong Where TenPhuong like '"+tenPhuong+"'";
                OleDbDataReader dr = da.ExecuteQuery(sql);
                if (dr.Read())
                {
                    dtoPhuong.MaPhuong = (int)dr["MaPhuong"];
                    dtoPhuong.TenPhuong = dr["TenPhuong"].ToString();
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return dtoPhuong;
        }
        
        public static bool ThemPhuong(DTOPhuong dtoP)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                if (!dtoP.TenPhuong.Equals(""))
                {
                    da.Open();
                    da.BeginTran();
                    String sql = "Insert Into TenPhuong(TenPhuong) Values ('" + dtoP.TenPhuong + "')";
                    da.ExecuteNonQuery(sql);
                    sql = "Select @@IDENTITY";
                    dtoP.MaPhuong = (int)da.ExecuteScalar(sql);
                    da.CommitTran();
                    kq = true;
                }
            }
            catch (Exception ex)
            {
                da.RollBackTran();
                throw ex;
            }
            return kq;
        }        
        public static bool CapNhatPhuong(DTOPhuong dtoP)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                if (!dtoP.TenPhuong.Equals(""))
                {
                    da.Open();
                    String sql = "Update TenPhuong Set TenPhuong = '" + dtoP.TenPhuong + "' Where MaTenPhuong = " + dtoP.MaPhuong;
                    da.ExecuteNonQuery(sql);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }        
        public static bool XoaPhuong(DTOPhuong dtoP)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                if (!dtoP.TenPhuong.Equals(""))
                {
                    da.Open();
                    String sql = "Delete From TenPhuong Where MaTenPhuong = " + dtoP.MaPhuong;
                    da.ExecuteNonQuery(sql);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
        public static int LayMaPhuong(String tenPhuong)
        {
            int kq = 0;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select MaPhuong From Phuong Where TenPhuong Like '" + tenPhuong + "'";
                kq = int.Parse("0" + da.ExecuteScalar(sql));
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
    }
}
